from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime, DECIMAL, Enum, ForeignKey, Text
from sqlalchemy.orm import relationship
from werkzeug.security import generate_password_hash, check_password_hash

from extension import db


# 用户表
class User(db.Model):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False, comment='用户名')
    password_hash = Column(String(256), nullable=False, comment='密码哈希')
    email = Column(String(50), comment='电子邮箱')
    created_at = Column(DateTime, default=datetime.utcnow, comment='创建时间')
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        return check_password_hash(self.password_hash, password)


# 学生表
class Student(db.Model):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, comment='学生姓名')
    studentId = Column(String(20), unique=True, nullable=False, comment='学生学号')
    gender = Column(Enum('男', '女'), nullable=False, comment='学生性别')
    grade = Column(String(10), nullable=False, comment='年级')
    class_id = Column(Integer, ForeignKey('classes.id'), nullable=False, comment='班级ID')
    major = Column(String(50), nullable=False, comment='学生专业')
    phone = Column(String(20), nullable=False, comment='联系方式')
    gpa = Column(DECIMAL(3, 2), default=0.00, comment='平均绩点')

    # 关联班级表
    student_class = relationship('Clazz', back_populates='students')
    # 关联成绩表
    scores = relationship('Score', back_populates='student')


# 部门表
class Department(db.Model):
    __tablename__ = 'departments'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False, comment='部门名称')

    # 关联教师表
    teachers = relationship('Teacher', back_populates='department')


# 教师表
class Teacher(db.Model):
    __tablename__ = 'teachers'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, comment='教师姓名')
    teacherId = Column(String(20), unique=True, nullable=False, comment='教师工号')
    gender = Column(Enum('男', '女'), nullable=False, comment='教师性别')
    title = Column(String(20), nullable=False, comment='教师职称')
    department_id = Column(Integer, ForeignKey('departments.id'), nullable=False, comment='所属部门ID')
    phone = Column(String(20), nullable=False, comment='联系方式')

    # 关联部门表
    department = relationship('Department', back_populates='teachers')
    # 关联课程表
    courses = relationship('Course', back_populates='teacher')
    # 关联班级表（作为班主任）
    head_classes = relationship('Clazz', back_populates='head_teacher')


# 课程表
class Course(db.Model):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, comment='课程名称')
    courseId = Column(String(20), unique=True, nullable=False, comment='课程编号')
    credits = Column(Integer, nullable=False, comment='课程学分')
    hours = Column(Integer, nullable=False, comment='课程学时')
    teacher_id = Column(Integer, ForeignKey('teachers.id'), nullable=False, comment='授课教师ID')
    type = Column(Enum('基础课','必修课', '选修课', '实践课'), nullable=False, comment='课程类型')
    description = Column(Text, comment='课程描述')

    # 关联教师表
    teacher = relationship('Teacher', back_populates='courses')
    # 关联成绩表
    scores = relationship('Score', back_populates='course')


# 成绩表
class Score(db.Model):
    __tablename__ = 'scores'
    id = Column(Integer, primary_key=True)
    student_id = Column(Integer, ForeignKey('students.id'), nullable=False, comment='学生ID')
    course_id = Column(Integer, ForeignKey('courses.id'), nullable=False, comment='课程ID')
    score = Column(Integer, nullable=False, comment='成绩')
    date = Column(DateTime, nullable=False, comment='评分日期')
    remark = Column(Text, comment='备注')

    # 关联学生表
    student = relationship('Student', back_populates='scores')
    # 关联课程表
    course = relationship('Course', back_populates='scores')


# 班级表
class Clazz(db.Model):
    __tablename__ = 'classes'
    id = Column(Integer, primary_key=True)
    classId = Column(String(10), unique=True, nullable=False, comment='班级编号')
    name = Column(String(50), nullable=False, comment='班级名称')
    grade = Column(String(10), nullable=False, comment='年级')
    major = Column(String(50), nullable=False, comment='专业')
    head_teacher_id = Column(Integer, ForeignKey('teachers.id'), nullable=False, comment='班主任ID')

    # 关联教师表（班主任）
    head_teacher = relationship('Teacher', back_populates='head_classes')
    # 关联学生表
    students = relationship('Student', back_populates='student_class')